import xlrd
import xlwt
import os


class excel:
    def __init__(self, excelFilePath):
        self.excelFile = xlrd.open_workbook(excelFilePath)
        self.excelWrite = xlwt.Workbook()
        self.table = self.excelWrite.add_sheet('sheet')
        self.excelTable = None

    # 获取所有sheet名字
    def getTables(self):
        return self.excelFile.sheet_names()
    # 获取第一个sheet名字
    def getfirstTables(self):
        return self.excelFile.sheets()[0]

    # 更改sheet
    def changeTable(self, tableName):
        self.excelTable = self.excelFile.sheet_by_name(tableName)

    def getRowNum(self):
        return self.excelTable.nrows

    def getRowValues(self, rowIndex):
        return self.excelTable.row_values(rowIndex)

    def getSheetsNum(self):
        return self.excelFile.nsheets

    def saveExcel(self,name):
        return self.saveExcel(name)

    def writeSheets(self,start,end,data):
        self.table.write(start,end,data)
        #self.excelWrite.save(name)

    """
    指定查询列，查询值 查找excel是否存在对应行数据，返回一行 list
    col_index ：指定的列
    search_str ： 指定列查找的值
    return 记录，所在数据所在sheet页。 找不到数据时sheet为最大
    """

    def findData(self, col_index, search_str, *args):
        # 是否找见
        result = []
        # 获取sheet数量
        sheetsNum = self.excelFile.nsheets
        # print(sheetsNum)
        for sheetNum in range(0, sheetsNum):
            # 获取工作表
            sheet = self.excelFile.sheet_by_index(sheetNum)
            # 获取工作表的总行数
            nrows = sheet.nrows
            # 循环遍历每一行
            for i in range(1, nrows):
                # 获取指定列的值
                cell_value = sheet.cell_value(i, col_index)
                # 判断是否包含指定字符串
                if search_str == cell_value:
                    # 输出符合条件的记录
                    result = sheet.row_values(i)
                    break

        return result, sheetNum

    """
    文件第一行 根据列名返回列所在位置, list
    """

    def findIndex(self, sheetNum, sheetList, *args):
        # 获取sheet数量
        self.excelFile.sheet_by_name(self.excelFile.sheet_names()[sheetNum])
        list = []
        for i in sheetList:
            find_index = self.excelTable.row_values(0).index(i)
            list.append(find_index)
        return list

    """
    取出excel数据
    *args: 输入待取出的值，list
    **kwargs：条件
    """

    def getExcelData(self, *args, **kwargs):
        lists = list(args)
        self.changeTable(self.getTables()[0])
        new_list = self.findIndex(0, lists)

        """
        循环遍历excel中的数据放入列表中，只支持第一个sheet页
        """
        # 获取工作表
        sheet = self.excelFile.sheet_by_index(0)
        # 获取工作表的总行数
        nrows = sheet.nrows
        # 取出列值放入新列表
        resultList1 = []

        # 循环遍历每一行
        for i in range(1, nrows):
            result1 = []
            for a in new_list:
                if len(kwargs)==0:
                    # 获取指定列的值
                    cell_value = sheet.cell_value(i, a)
                    result1.append(cell_value)
                else:
                    b=0
                    for dict in kwargs:
                        dictList = []
                        dictList.append(dict)
                        if sheet.cell_value(i, self.findIndex(0,dictList)[0])==kwargs[dict]:
                            cell_value = sheet.cell_value(i, a)
                            b+=1
                    if b == len(kwargs):
                        result1.append(cell_value)
            #print(result1)
            if len(result1) !=0:
                resultList1.append(result1)
        return resultList1,len(resultList1)





if __name__ == '__main__':
    #excelpath = input('目标excel文件地址:')
    oo = excel("/Users/chenliang/Downloads/BillingStatement_20240311170844.xlsx")
    # 根据列查询数据是否存在
    # list, sheetNum = oo.findData(0, '2024030517213400001')
    # print(list,sheetNum)
    a,b=oo.getExcelData('Download time')
    result=[]
    result, sheetNum=oo.findData(0,'Invoice No.')
    print(a)
    print(len(a),a.index(['Invoice No.']),len(a)-a.index(['Invoice No.'])-1)
